/**
 * 
 */
package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.DraughtBeer;
import ModelLayer.Drink;
import ModelLayer.Product;

/**
 * @author cbjerg
 *
 */
public class ProductDB implements ProductDBIF {
	
	private  Connection con;
    /** Creates a new instance of DBProduct */
    public ProductDB() {
      con = DbConnection1.getInstance().getDBcon();
    }

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#getAllProducts(boolean)
	 */
	public ArrayList<Product> getAllProducts(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#findProduct(int, boolean)
	 */
	public Product findProduct(int prodID, boolean retrieveAssociation) {
		String wClause = "  productID = '" + prodID + "'";
        return singleWhere(wClause, retrieveAssociation);
	}
	
	public DraughtBeer findDraughtBeer(int prodID, boolean retrieveAssociation) {
		String wClause = "  productID = '" + prodID + "'";
        return singleWhereDraught(wClause, retrieveAssociation);
	}
	
	public ArrayList<DraughtBeer> getAllDraughts(boolean retrieveAssociation) {
		return miscWhereDraught("", retrieveAssociation);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#searchProductName(java.lang.String, boolean)
	 */
	public ArrayList<Product> searchProductName(String name, boolean retrieveAssociation) {
		String wClause = "productname like '%" + name + "%'";
        System.out.println("SearchProductName " + wClause);
        return miscWhere(wClause, retrieveAssociation);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#searchProductDescription(java.lang.String, boolean)
	 */
	public Product searchProductDescription(String prodDescription, boolean retrieveAssociation) {
		String wClause = "productDescription like '%" + prodDescription + "%'";
        System.out.println("SearchProductDescription " + wClause);
        return singleWhere(wClause, retrieveAssociation);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#insertProduct(ModelLayer.Product)
	 */
	public int insertProduct(Product pro) throws Exception {
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(productID) from product");
        nextID = nextID + 1;
        System.out.println("next productID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO product(productID, productName, productDescription, inStock, defaultPrice, productType, minStock)  VALUES('"+
	  		nextID + "','" +
			pro.getName()  + "','"  +
		    pro.getDescription() + "','"  +
            pro.getInStock() + "','"  +
		    pro.getDefaultPrice() + "','"  +
            pro.getProductType() + "','"  +
		    pro.getMinStock() + "')";

       System.out.println("insert : " + query);
 
      try{ // insert new clothing
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Product ikke oprettet");
          throw new Exception ("product is not inserted correct");
       }
       return(rc);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#updateProduct(ModelLayer.Product)
	 */
	@Override
	public int updateProduct(Product pro) {
		Product proObj  = pro;
		int rc=-1;
		
		String query="UPDATE product SET "+
				"productName ='"+ proObj.getName()+"', "+
				"productDescription ='"+proObj.getDescription()+"', "+
				"inStock ='"+proObj.getInStock()+"', "+
				"defaultPrice  ='"+proObj.getDefaultPrice()+"', "+
				"ProductType ='"+proObj.getProductType()+"', "+
				"MinStock ='"+proObj.getMinStock()+"' "+
		 	  
		          " WHERE productID = '"+ proObj.getProductId() + "'";
		
		
                System.out.println("Update Product query:" + query);
  		try{ // update product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#insertDraught(ModelLayer.DraughtBeer)
	 */
	@Override
	public int insertDraught(DraughtBeer dra) throws Exception {
		int nextID = GetMax.getMaxId("Select max(productID) from product");
        nextID = nextID + 1;
        System.out.println("next productID = " +  nextID);
  
       int rc = -1;
	   String query1="INSERT INTO product(productID, productName, productDescription, inStock, defaultPrice, productType, minStock)  VALUES('"+
	  		nextID + "','" +
			dra.getName()  + "','"  +
		    dra.getDescription() + "','"  +
            dra.getInStock() + "','"  +
		    dra.getDefaultPrice() + "','"  +
            dra.getProductType() + "','"  +
		    dra.getMinStock() + "')";

       System.out.println("insert Product : " + query1);
       
       String query2="INSERT INTO DraughtBeer(DraughtPid, WeekendPrice, HalfPintPrice, StaffPrice, Vip1Price, Vip2Price, FixedEventPrice)  VALUES('"+
   	  		nextID + "','" +
		    dra.getWeekendPrice() + "','"  +
		    dra.getHalfPintPrice() + "','"  +
		    dra.getStaffPrice() + "','"  +
		    dra.getVip1Price() + "','"  +
		    dra.getVip2Price() + "','"  +
		    dra.getFixedEventPrice() + "')";
       
       System.out.println("insert Draught : " + query2);
      try{ // insert new product
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query1);
     	 rc = stmt.executeUpdate(query2);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Draught Beer ikke oprettet");
          throw new Exception ("Draught Beer is not inserted correct");
       }
       return(rc);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#updateDraught(ModelLayer.DraughtBeer)
	 */
	public int updateDraught(DraughtBeer dra) {
		DraughtBeer draObj  = dra;
		int rc=-1;
		
		String query1="UPDATE product SET "+
				"productName ='"+ draObj.getName()+"', "+
				"productDescription ='"+draObj.getDescription()+"', "+
				"inStock ='"+draObj.getInStock()+"', "+
				"defaultPrice  ='"+draObj.getDefaultPrice()+"', "+
				"ProductType ='"+draObj.getProductType()+"', "+
				"MinStock   ='"+draObj.getMinStock()+"' "+
		 	  
		          " WHERE productID = '"+ draObj.getProductId() + "'";
		
		
                System.out.println("Update DraughtBeer query:" + query1);
                
                String query2="UPDATE DRAUGHTBEER SET "+
                		"WeekendPrice   ='"+draObj.getWeekendPrice()+"', "+
        				"HalfPintPrice   ='"+draObj.getHalfPintPrice()+"', "+
        				"StaffPrice   ='"+draObj.getStaffPrice()+"', "+
        				"Vip1Price   ='"+draObj.getVip1Price()+"', "+
        				"Vip2Price   ='"+draObj.getVip2Price()+"', "+
        				"FixedEventPrice ='"+draObj.getFixedEventPrice()+"' "+
        		 	  
        		          " WHERE DraughtPid = '"+ draObj.getProductId() + "'";
                
  		try{ // update Gun
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query1);
	 	 	rc = stmt.executeUpdate(query2);
	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#insertDrink(ModelLayer.Drink)
	 */
	public int insertDrink(Drink dri) throws Exception {
		int nextID = GetMax.getMaxId("Select max(productID) from product");
        nextID = nextID + 1;
        System.out.println("next productID = " +  nextID);
        ArrayList<Product> products = dri.getProducts();
  
       int rc = -1;
	   String query1="INSERT INTO product(productID, productName, productDescription, inStock, defaultPrice, productType, minStock, WeekendPrice, HalfPintPrice, StaffPrice, Vip1Price, Vip2Price)  VALUES('"+
	  		nextID + "','" +
			dri.getName()  + "','"  +
		    dri.getDescription() + "','"  +
            dri.getInStock() + "','"  +
		    dri.getDefaultPrice() + "','"  +
            dri.getProductType() + "','"  +
		    dri.getMinStock() + "')";

       System.out.println("insert Product : " + query1);
       try{ // insert new product
           Statement stmt = con.createStatement();
           stmt.setQueryTimeout(5);
      	  rc = stmt.executeUpdate(query1);
           stmt.close();
       }//end try
        catch(SQLException ex){
           System.out.println("Product ikke oprettet");
           throw new Exception ("Product is not inserted correct");
        }
       
       for(Product product : products)
       {
    	   
       String query2="INSERT INTO Drink(DrinkPid, DrinkContainsId)  VALUES('"+
   	  		nextID + "','" +
		    product.getProductId() + "')";
       
       System.out.println("insert Draught : " + query2);
      try{ // insert new product
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	 rc = stmt.executeUpdate(query2);
          stmt.close();
      
      }//end try
       catch(SQLException ex){
          System.out.println("Drink ikke oprettet");
          throw new Exception ("Drink is not inserted correct");
       }}
       return rc;
       
	}

	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#updateDrink(ModelLayer.Drink)
	 */


	/* (non-Javadoc)
	 * @see DBLayer.ProductDBIF#deleteProduct(int)
	 */
	public int deleteProduct(int prodID) {
		int rc=-1;
		  
	  	String query="DELETE FROM product WHERE productID = '" +
	  			prodID + "'";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
		return(rc);
	}

	public int downSizeInventory(int prodID, int amount) {
		Product proObj = findProduct(prodID, true);
		int amount2 = proObj.getInStock() - amount;
		
		int rc=-1;
		String query="UPDATE product SET "+
				"InStock ='"+amount2+"' "+
			 	  
		          " WHERE productID = '"+prodID + "'";
		
		
                System.out.println("Downsize inventory query:" + query);
  		try{ // update product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	 	System.out.println("downsize exception in product db: "+ex);
	  	}
		return(rc);
	}
		

    
	
	private Product singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Product proObj = new Product();
        String query =  buildQuery(wClause);
        System.out.println("ProductDB -singleWhere " + query); 
        try{ // read from Product
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       proObj = buildProduct(results);
               
           }//end if
	   stmt.close();
        }//end try
	catch(Exception e){
	   System.out.println("Query exception - select product : "+e);
	   e.printStackTrace();
        }
	return proObj;
    }
	
	 //miscWhere is used when more than one Product is selected and build
    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		
		String query =  buildQuery(wClause);
                System.out.println("ProductDB " + query); 
  		try{ // read from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
				Product proObj = new Product();
			 proObj = buildProduct(results);	
                         list.add(proObj);
                         //missing tes on retriveAssociation
			}//end while
			stmt.close();
			
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select product : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private DraughtBeer singleWhereDraught(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        DraughtBeer proObj = new DraughtBeer();
        String query =  buildQueryDraught(wClause);
        System.out.println("ProductDB -singleWhereDraught " + query); 
        try{ // read from Product
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       proObj = buildDraught(results);
               
           }//end if
	   stmt.close();
        }//end try
	catch(Exception e){
	   System.out.println("Query exception - select product : "+e);
	   e.printStackTrace();
        }
	return proObj;
    }
	
	 //miscWhere is used when more than one Product is selected and build
    private ArrayList<DraughtBeer> miscWhereDraught(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<DraughtBeer> list = new ArrayList<DraughtBeer>();
		
		String query =  buildQueryDraught(wClause);
                System.out.println("ProductDB " + query); 
  		try{ // read from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
				DraughtBeer proObj = new DraughtBeer();
			 proObj = buildDraught(results);	
                         list.add(proObj);
                         //missing tes on retriveAssociation
			}//end while
			stmt.close();
			
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select product : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Product buildProduct(ResultSet results)
    {
    	Product proObj = new Product();

       
         try{
               proObj.setProductId(results.getInt("productID"));
               proObj.setName(results.getString("productName"));
               proObj.setDescription(results.getString("productDescription"));
               proObj.setInStock(results.getInt("InStock"));
               proObj.setDefaultPrice(results.getDouble("defaultPrice"));
               proObj.setProductType(results.getString("productType"));
               proObj.setMinStock(results.getInt("MinStock"));               
               
          }
         catch(Exception e)
         {
             System.out.println("building product object");
         }
        
        return proObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT productID, productName, ProductDescription, InStock, defaultPrice, ProductType, MinStock FROM product "; 
            
           

		if (wClause.length()>0)
			query = query + "  where " + wClause;
			
		return query;
	}
    
    private DraughtBeer buildDraught(ResultSet results)
    {
    	DraughtBeer proObj = new DraughtBeer();

       
         try{
               proObj.setProductId(results.getInt("productID"));
               proObj.setName(results.getString("productName"));
               proObj.setDescription(results.getString("productDescription"));
               proObj.setInStock(results.getInt("InStock"));
               proObj.setDefaultPrice(results.getDouble("defaultPrice"));
               proObj.setProductType(results.getString("productType"));
               proObj.setMinStock(results.getInt("MinStock")); 
               proObj.setWeekendPrice(results.getDouble("WeekendPrice"));
               proObj.setHalfPintPrice(results.getDouble("HalfPintPrice"));
               proObj.setStaffPrice(results.getDouble("StaffPrice"));
               proObj.setVip1Price(results.getDouble("Vip1Price"));
               proObj.setVip2Price(results.getDouble("Vip2Price"));
               proObj.setFixedEventPrice(results.getDouble("FixedEventPrice"));
               
          }
         catch(Exception e)
         {
             System.out.println("building DraughtBeer object");
         }
        
        return proObj;
        
    }
    
    private String buildQueryDraught(String wClause)
	{
            String query="SELECT productID, productName, ProductDescription, InStock, defaultPrice, ProductType, MinStock, WeekendPrice, HalfPintPrice, StaffPrice, Vip1Price, Vip2Price, FixedEventPrice FROM product, draughtbeer where productid=draughtpid "; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}
}
